﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace lab2
{
    public partial class reisy : Form
    {
        public reisy()
        {
            InitializeComponent();
        }

        private void reisy_Load(object sender, EventArgs e)
        {
            this.changeBil();
        }

        private void changeBil()
        {
            int id;
            if (dataGridView1.CurrentRow != null)
            {
                DataGridViewRow currentRow = dataGridView1.CurrentRow;
                id = Convert.ToInt32(currentRow.Cells[0].Value);
            }
            else
            {
                id = 1;
            }


            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = database\\passengers.mdb");
            cn.Open();
            cm = new OleDbCommand("SELECT Билеты.seat, Пассажиры.first_name, Пассажиры.second_name FROM Пассажиры INNER JOIN (Рейсы INNER JOIN Билеты ON Рейсы.id_flight = Билеты.id_flight) ON Пассажиры.id_passenger = Билеты.id_passenger WHERE Билеты.id_flight = " + id, cn);
            da = new OleDbDataAdapter();
            da.SelectCommand = cm;
            dt = new DataTable();
            da.Fill(dt);
            dataGridView2.DataSource = dt;
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
        OleDbConnection cn;
        OleDbCommand cm;
        OleDbDataAdapter da;
        DataTable dt;
        public void testFunction()
        {
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = database\\passengers.mdb");
            cn.Open();
            cm = new OleDbCommand("SELECT * FROM рейсы", cn);
            da = new OleDbDataAdapter();
            da.SelectCommand = cm;
            dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            //textBox1.DataBindings.Add("Text", dt, "first_name");
            /*DataView view = dt.DefaultView;
            for (int i = 0; i < view.Count; i++)
            {
                textBox1.DataBindings.Add("Text", view[i][0], "Name");
            }*/
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();

        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = database\\passengers.mdb");
            cn.Open();
            string test = "UPDATE Рейсы SET `" + dataGridView1.Columns[e.ColumnIndex].Name + "`='" + dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString() + "' WHERE Рейсы.id_flight=" + dataGridView1[0, e.RowIndex].Value.ToString();
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int id;
            if (dataGridView1.SelectedRows.Count > 0)
            {
                DataGridViewRow currentRow = dataGridView1.SelectedRows[0];
                id = Convert.ToInt32(currentRow.Cells[0].Value);
            }
            else
            {
                id = 0;
            }
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = database\\passengers.mdb");
            cn.Open();
            string test = "DELETE * FROM Билеты WHERE Билеты.id_flight = " + id;
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cm.Dispose();
            test = "DELETE * FROM Рейсы WHERE `id_flight` = " + id;
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
            dataGridView1.Rows.RemoveAt(dataGridView1.CurrentRow.Index);
        }

        private void dataGridView1_KeyDown(object sender, KeyEventArgs e)
        {
            this.changeBil();
        }

        private void dataGridView1_KeyPress(object sender, KeyPressEventArgs e)
        {

            this.changeBil();
        }

        private void dataGridView1_KeyUp(object sender, KeyEventArgs e)
        {

            this.changeBil();
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            this.changeBil();
        }
    }
}
